﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;

/***
*机器名：lpc
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：CompanysHrsDAL
*系统时间：3/25/2012 7:41:30 PM
*当前用户：lpc
*创建时间：2012
*
*修改时间：2012年5月11日13:52:27
*修改事件：
***/
namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class CompanysHrsDAL : ICompanysHrsDAL
    {
        private const string SQL_SELECT_COMPANYSHRS = " select CompanyID, UserId  "
                                                       + "   from CompanysHrs ";
        private const string SQL_SELECT_COMPANYSHRS_BY_COMPANYID = " select CompanyID, UserId  "
                                                                + "   from CompanysHrs "
                                                                + "  where CompanyID=@CompanyID  ";
        private const string SQL_DELETE_COMPANYSHRS_BY_COMPANYID = " delete from CompanysHrs "
                                                                + "  where CompanyID=@CompanyID ";
        private const string SQL_UPDATE_COMPANYSHRS_BY_COMPANYID = " update CompanysHrs set "
                                                                + "  CompanyID = @CompanyID , "
                                                                + "  UserId = @UserId  "
                                                                + "  where CompanyID=@CompanyID ";
        private const string SQL_INSERT_COMPANYSHRS = " insert into CompanysHrs("
                                                     + " CompanyID,UserId"
                                                     + " ) values ("
                                                     + " @CompanyID,@UserId";


        private const string FIELD_CompanyID = "@CompanyID";
        private const string FIELD_UserId = "@UserId";
        public bool InsertCompanysHrs(CompanysHrs cmodel)
        {
            bool flag = false;
            SqlParameter[] CompanysHrsParms = GetCompanysHrsParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            CompanysHrsParms[0].Value = cmodel.CompanyID;
            CompanysHrsParms[1].Value = cmodel.UserId;

            foreach (SqlParameter parm in CompanysHrsParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_COMPANYSHRS;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool DeleteCompanysHrs(string CompanyID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_CompanyID, SqlDbType.NVarChar, 128);
            parm.Value = CompanyID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_COMPANYSHRS_BY_COMPANYID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool UpdateCompanysHrs(CompanysHrs cmodel)
        {
            bool flag = false;
            SqlParameter[] CompanysHrsParms = GetCompanysHrsParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            CompanysHrsParms[0].Value = cmodel.CompanyID;
            CompanysHrsParms[1].Value = cmodel.UserId;

            foreach (SqlParameter parm in CompanysHrsParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_COMPANYSHRS_BY_COMPANYID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public CompanysHrs SelectCompanysHrsByCompanyID(string CompanyID)
        {
            CompanysHrs cmodel = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_CompanyID, SqlDbType.NVarChar, 128);
            parm.Value = CompanyID;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_COMPANYSHRS_BY_COMPANYID, parm))
            {
                if (dr.Read())
                {
                    cmodel = new CompanysHrs();
                    if (dr.HasRows == true)
                    {
                        if (dr["CompanyID"] != null && dr["CompanyID"].ToString() != "")
                        {
                            cmodel.CompanyID =  dr["CompanyID"].ToString();
                        }
                        if (dr["UserId"] != null && dr["UserId"].ToString() != "")
                        {
                            cmodel.UserId = dr["UserId"].ToString();
                        }
                    }
                }
            }
            return cmodel;
        }
        public List<CompanysHrs> SelectCompanysHrs()
        {
            List<CompanysHrs> selectlist = new List<CompanysHrs>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_COMPANYSHRS))
            {
                while (dr.Read())
                {
                    CompanysHrs cmodel = new CompanysHrs();
                    //写添赋值语句
                    if (dr["CompanyID"] != null && dr["CompanyID"].ToString() != "")
                    {
                        cmodel.CompanyID = dr["CompanyID"].ToString();
                    }
                    if (dr["UserId"] != null && dr["UserId"].ToString() != "")
                    {
                        cmodel.UserId = dr["UserId"].ToString();
                    }

                    selectlist.Add(cmodel);
                }
            }
            return selectlist;
        }
        private SqlParameter[] GetCompanysHrsParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter(FIELD_CompanyID, SqlDbType.NVarChar,128),
					new SqlParameter(FIELD_UserId, SqlDbType.NVarChar,128)};
            return parms;
        }

    }
}